package com.aiflow.Action;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import com.aiflow.db.ConnDB;
import com.aiflow.util.DownExcell;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

//  通话流水查询
//  告警查询 
public class CallStreamAction {

	// 查询 告警信息
	public void findCallStream() {
		HttpServletRequest req = ServletActionContext.getRequest();
		int start = Integer.parseInt(req.getParameter("start"));
		int limit = Integer.parseInt(req.getParameter("limit"));
		int end = start + limit;
		int begin = start + 1;
		String startTime = (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String tele = (String) req.getParameter("tele"); // 返回值
		if (startTime != null && !startTime.equals("")) {
			startTime = (startTime.split("T"))[0];
		}
		if (endTime != null && !endTime.equals("")) {
			endTime = (endTime.split("T"))[0];
		}

		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if (startTime != null && !startTime.equals("")) {
			whereSql += " and   sendTime >= '" + startTime + "' ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  sendTime < '" + endTime + "' ";
		}
		if (tele != null && !tele.equals("")) {
			whereSql += " and  tel = '" + tele + "' ";
		}

		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		try {
			String sql = "select * from "
					+ "("
					+ " select a.id,alarmId,MVB_CALLER,u.user_name,tel,a.alarmTargetId,d.name,sendTime, calltime,answertime ,endtime,duration,"
					+ " (row_number() over(ORDER BY a.id desc)) rb,snumbe  from t_s_alarmRecord a,dbo.t_s_userConfigure u,dictionary d "
					+ " where a.userId = u.id and a.alarmTargetId = d.id and disposition = 'ANSWERED' "
					+ whereSql + ") tt " + "where tt.rb between " + begin
					+ " and " + end;

			sqlCount = "select count(1) "
					+ " from t_s_alarmRecord a,dbo.t_s_userConfigure u,dictionary d where a.userId = u.id and a.alarmTargetId = d.id  and disposition = 'ANSWERED' "
					+ whereSql;
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				Map<String, String> mapdata = new HashMap<String, String>();
				mapdata.put("id", rs.getString(1));
				mapdata.put("alarmId", rs.getString(2));
				mapdata.put("MVB_CALLER", rs.getString(3));
				mapdata.put("userName", rs.getString(4));
				mapdata.put("tel", rs.getString(5));
				mapdata.put("name", rs.getString(7));
				mapdata.put("sendTime", rs.getString(8));
				mapdata.put("calltime", rs.getString(9));
				mapdata.put("answertime", rs.getString(10));
				mapdata.put("endtime", rs.getString(11));
				mapdata.put("duration", rs.getString(12));
				mapdata.put("snumbe", rs.getString(14));

				datalist.add(mapdata);
			}
			int index = db.GetInfoCount(sqlCount);
			datas = JSONArray.fromObject(datalist);
			json.put("success", true);
			json.put("datas", datas);
			json.put("results", index);
			HttpServletResponse response = ServletActionContext.getResponse();
			response.setCharacterEncoding("UTF-8");
			PrintWriter out = response.getWriter();
			out.print(json);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception ex1) {
				ex1.printStackTrace();

			}
		}

	}

	public void down() {
		HttpServletRequest req = ServletActionContext.getRequest();
		HttpServletResponse res = ServletActionContext.getResponse();
		String startTime = (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String tele = (String) req.getParameter("tele"); // 返回值
		if (startTime != null && !startTime.equals("")) {
			startTime = (startTime.split("T"))[0];
		}
		if (endTime != null && !endTime.equals("")) {
			endTime = (endTime.split("T"))[0];
		}

		ConnDB db = new ConnDB();
		ResultSet rs = null;
		JSONObject json = new JSONObject();
		List<Map<String, String>> datalist = new ArrayList<Map<String, String>>();
		JSONArray datas = new JSONArray();
		String whereSql = "";
		if (startTime != null && !startTime.equals("")) {
			whereSql += " and   sendTime >= '" + startTime + "' ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  sendTime < '" + endTime + "' ";
		}
		if (tele != null && !tele.equals("")) {
			whereSql += " and  tel = '" + tele + "' ";
		}

		String sqlCount = "";
		Connection con = db.getConnection();
		PreparedStatement ps = null;

		String sql = " select alarmId 告警编号,MVB_CALLER,u.user_name 人员,tel 手机,d.name 传报对象,sendTime 发送时间, calltime 呼叫时间,answertime 响应时间 ,endtime 结束时间,duration 计时"
				+ "  from t_s_alarmRecord a,dbo.t_s_userConfigure u,dictionary d "
				+ " where a.userId = u.id and a.alarmTargetId = d.id and disposition = 'ANSWERED' "
				+ whereSql;
		DownExcell excel = new DownExcell();
		excel.getData(sql, req, res, "通话流水查询");
	}
	
	public void Supervisedown() {
		HttpServletRequest req = ServletActionContext.getRequest();
		HttpServletResponse res = ServletActionContext.getResponse();
		String startTime = (String) req.getParameter("startTime");
		String endTime = (String) req.getParameter("endTime");
		String tele = (String) req.getParameter("tele"); // 返回值
		String region = req.getParameter("region");
		if (startTime != null && !startTime.equals("")) {
			startTime = (startTime.split("T"))[0];
		}
		if (endTime != null && !endTime.equals("")) {
			endTime = (endTime.split("T"))[0];
		}
		String whereSql = "";
		if (startTime != null && !startTime.equals("")) {
			//whereSql += " and   sendTime >= " + startTime + "' ";
			whereSql += " and   sendTime >= " + startTime + " ";
		}
		if (endTime != null && !endTime.equals("")) {
			whereSql += " and  sendTime < " + endTime + " ";
			whereSql += " and  sendTime < '" + endTime + " ";
		}
		if (tele != null && !tele.equals("")) {
			whereSql += " and  tel = '" + tele + "' ";
		}
		if (region != null && !region.equals("")) {
			whereSql += " and   region_name like '%" + region + "%' ";
		}
		String sql = " select superviseId 工单编号,MVB_CALLER,u.user_name 人员,tel 手机,d.name 工单对象,sendTime 派单时间, calltime 呼叫时间,answertime 响应时间 ,endtime 结束时间,duration 计时,context as 通话内容,case supervise_status when 770 then '受理' when 771 then '回复' when 773 then '实时督办' else '过程' end as 工单状态,region_name as 地市 "
				+ "  from t_s_superviseRecord a,dictionary d "
				+ " where a.superviseTargetId = d.id and disposition = 'ANSWERED' "
				+ whereSql+
				"order by 派单时间";
		DownExcell excel = new DownExcell();
		excel.getData(sql, req, res, "工单通话流水查询");
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
